﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wwtfly.BBS.BackModels.Models.Users;
using Wwtfly.BBS.Framework.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Wwtfly.DBUtility;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Wwtfly.BBS.BackModels.Models.Users.Right;

namespace Wwtfly.BBS.BackDAL.Users
{
    /// <summary>
    /// 用户信息
    /// </summary>
    public class UsersDAL
    {

        #region 属性

        /// <summary>
        /// 分页获取 用户信息 存储过程名
        /// </summary>
        private static readonly string GetUsersInfoPageList_Select = "proc_SelectUsers";

        /// <summary>
        /// 分页获取 用户信息 存储过程名
        /// </summary>
        private static readonly string GetUsersInfo_Select_ByUserName = "proc_SelectUsersByUserName";

        /// <summary>
        /// 根据用户ID 获取用户的角色, 
        /// </summary>
        private static readonly string GetUARoleInfoListSelectByUserId = @"
            WITH t AS (
	     
	      SELECT ui.RoleID, ui.RoleName, ui.RoleCode, ui.RoleType, ui.IsSystemRole,
	             ui.Comments,uor.UserID
	        FROM UARoleInfo ui
	      INNER JOIN UAUserOfRole uor ON ui.RoleID = uor.RoleID
            WHERE uor.UserID=@UserID
      )
        SELECT * FROM t;
            ";

          /// <summary>
        /// 根据用户ID 获取用户的权限集合
        /// </summary>
        private static readonly string GetUARightInfoListSelectByUserId = @"
        WITH t AS (
	      SELECT ui.RightID, ui.ModuleType, ui.RightCode, ui.Grade, ui.NodeType,
	             ui.ParentCode, ui.PathCode, ui.RightName, ui.OrderID, ui.LogFlag,
	             uri.RoleID 
	        FROM UARightInfo ui
	      INNER JOIN UARoleOfRight uor ON ui.RightCode=uor.RightCode
	      INNER JOIN UARoleInfo uri ON uri.RoleID = uor.RoleID
	      INNER JOIN UAUserOfRole uur  ON uor.RoleID=uur.RoleID
	      WHERE uur.UserID=@UserID
      )
        SELECT * FROM t ORDER BY t.OrderID DESC ;
        ";

        /// <summary>
        /// 根据用户ID 获取用户的角色,再获取角色的权限,再根据权限获取用户的菜单..
        /// </summary>
        private static readonly string GetUAMenuInfoListSelectByUserId = @"
            WITH t AS (
	      SELECT DISTINCT umi.MenuID,
	      umi.MenuType, 
	      umi.MenuLevel, umi.ParentMenuID
	      ,umi.PathCode
	      ,umi.MenuName,umi.OrderID
	      ,umi.ImageURL, umi.LinkURL
	      ,umi.OpenTarget, umi.IsVisible
		    FROM UAMenuInfo umi
	      INNER JOIN  UARightOfMenus uom ON umi.MenuID=uom.MenuID
	      INNER JOIN UARoleOfRight uor ON uom.RightCode=uor.RightCode
	      INNER JOIN UAUserOfRole uur  ON uor.RoleID=uur.RoleID
	      WHERE 
	      (uur.UserID=@UserID OR @UserID=-1)
	      AND umi.MenuType=@MenuType
	      AND umi.IsVisible=@IsVisible
      )
      SELECT 
      DISTINCT umi.MenuID,
	      umi.MenuType, 
	      umi.MenuLevel, umi.ParentMenuID
	      ,umi.PathCode
	      ,umi.MenuName,umi.OrderID
	      ,umi.ImageURL, umi.LinkURL
	      ,umi.OpenTarget, umi.IsVisible 
      FROM UAMenuInfo umi
      INNER JOIN t ON CHARINDEX('.'+umi.MenuID+'.','.'+t.PathCode+'.',1)>0
      ORDER BY umi.OrderID DESC
            ";

        



        /// <summary>
        /// 根据 ID 获取 用户信息
        /// </summary>
        public const string PM_USERS_SELECT_BY_ID = " SELECT [Id],[UserName],[TrueName],[UserIntegral],[UserPassword],[UserEmail],[UserEmailState],[UserMobile],[UserMobileState],[UserQQ],[UserMSN],[UserColourMoney],[UserMoney],[UserLoginTime],[UserRegisterTime],[UserUpdateTime],[UserImgUrl],[UserState],[UserLoginState],[UserRemark],[CreateTime],[CreateUser],[LastModifyTime],[LastModifyUser]" +
                                            " FROM [pm_users]" +
                                            " WHERE (" +
                                            " [Id] = @Id)";


        public const string PM_USERS_UPDATE = " UPDATE [pm_users] SET " +
                                        "[UserName]=@UserName,[TrueName]=@TrueName,[UserIntegral]=@UserIntegral,[UserPassword]=@UserPassword,[UserEmail]=@UserEmail,[UserEmailState]=@UserEmailState,[UserMobile]=@UserMobile,[UserMobileState]=@UserMobileState,[UserQQ]=@UserQQ,[UserMSN]=@UserMSN,[UserColourMoney]=@UserColourMoney,[UserMoney]=@UserMoney,[UserLoginTime]=@UserLoginTime,[UserRegisterTime]=@UserRegisterTime,[UserUpdateTime]=@UserUpdateTime,[UserImgUrl]=@UserImgUrl,[UserState]=@UserState,[UserLoginState]=@UserLoginState,[UserRemark]=@UserRemark,[CreateTime]=@CreateTime,[CreateUser]=@CreateUser,[LastModifyTime]=@LastModifyTime,[LastModifyUser]=@LastModifyUser" +
                                    " WHERE " +
                                        " [Id] = @Id";

        public const string PM_USERS_INSERT = " INSERT INTO [pm_users] (" +
                                         "[UserName],[TrueName],[UserIntegral],[UserPassword],[UserEmail],[UserEmailState],[UserMobile],[UserMobileState],[UserQQ],[UserMSN],[UserColourMoney],[UserMoney],[UserLoginTime],[UserRegisterTime],[UserUpdateTime],[UserImgUrl],[UserState],[UserLoginState],[UserRemark],[CreateTime],[CreateUser],[LastModifyTime],[LastModifyUser]" +
                                     ") VALUES (" +
                                         "@UserName,@TrueName,@UserIntegral,@UserPassword,@UserEmail,@UserEmailState,@UserMobile,@UserMobileState,@UserQQ,@UserMSN,@UserColourMoney,@UserMoney,@UserLoginTime,@UserRegisterTime,@UserUpdateTime,@UserImgUrl,@UserState,@UserLoginState,@UserRemark,@CreateTime,@CreateUser,@LastModifyTime,@LastModifyUser" +
                                     ");select @Id = @@Identity;";


        public const string PM_USERS_updatePwd = "UPDATE [pm_users] SET [UserPassword]=@UserPassword  where [Id] = @Id";

        public const string PM_USERS_updateMsg = "UPDATE [pm_users] SET [TrueName]=@TrueName,[UserEmail]=@UserEmail,[UserMobile]=@UserMobile,[UserQQ]=@UserQQ,[UserImgUrl]=@UserImgUrl where [Id] = @Id";
        #endregion

         /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="pmusers">pmusers实体</param>
        /// <returns></returns>
        public static bool UpdatePwd(UsersModel pmusers)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_USERS_updatePwd);
            db.AddInParameter(dbCommand, "@Id", DbType.Int32, DBConvert.ToDBValue(pmusers.Id));
            db.AddInParameter(dbCommand, "@UserPassword", DbType.String, DBConvert.ToDBValue(pmusers.UserPassword));
            return db.ExecuteNonQuery(dbCommand) > 0;
        }
        /// <summary>
        /// 分页 获取用户的相关信息
        /// </summary>
        /// <param name="pageParams">分页参数</param>
        /// <param name="searchModel">用户搜索 实体类</param>
        /// <returns></returns>
        public static PagedList<UsersModel> GetUsersInfoPageList(PageParams pageParams, UsersSearchModel searchModel)
        {
            IList<UsersModel> list = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(GetUsersInfoPageList_Select);
            DBParamsUtility.CreatePageParams(pageParams, db, dbCommand);
            int totalCount = 0;
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                list = Emit.ConvertToList<UsersModel>(dr);
                totalCount = DBParamsUtility.GetTotalCount(dbCommand);
            }
            return list.ToPagedList<UsersModel>(pageParams, totalCount);
        }


        /// <summary>
        /// 根据 用户名 获取用户的相关信息
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static UsersModel GetUsersInfo(string userName)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(GetUsersInfo_Select_ByUserName);
            db.AddInParameter(dbCommand, "@UserName", DbType.String, userName);
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                UsersModel user = Emit.ConvertTo<UsersModel>(dr);
                return user;
            }

        }


        /// <summary>
        /// 获取菜单 列表
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="menuType"></param>
        /// <returns></returns>
        public static IList<UAMenuInfoModel> GetUAMenuInfoList(int userId, string menuType)
        {
            using (IDataReader dr = DBHelper.ExecuteReader(GetUAMenuInfoListSelectByUserId,
                new SqlParameter() { ParameterName = "@UserId", SqlDbType = SqlDbType.Int, Value = userId },
                 new SqlParameter() { ParameterName = "@IsVisible", SqlDbType = SqlDbType.NVarChar, Value = 1 },
                new SqlParameter() { ParameterName = "@MenuType", SqlDbType = SqlDbType.NVarChar, Value = menuType }
                ))
            {
                return Emit.ConvertToList<UAMenuInfoModel>(dr);
            }
        }

        /// <summary>
        ///  获取 用户角色 集合
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static IList<UARoleInfoModel> GetUARoleInfoList(int userId)
        {
            using (IDataReader dr = DBHelper.ExecuteReader(GetUARoleInfoListSelectByUserId,
                new SqlParameter() { ParameterName = "@UserId", SqlDbType = SqlDbType.Int, Value = userId }
                ))
            {
                return Emit.ConvertToList<UARoleInfoModel>(dr);
            }
        }


        //获取 用户权限 集合
        /// <summary>
        ///  获取 用户权限 集合
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static IDictionary<string, int> GetUARightInfoList(int userId)
        {
            Dictionary<string, int> dic = new Dictionary<string, int>();
            using (IDataReader dr = DBHelper.ExecuteReader(GetUARightInfoListSelectByUserId,
                new SqlParameter() { ParameterName = "@UserId", SqlDbType = SqlDbType.Int, Value = userId }
                ))
            {
                while (dr.Read())
                {
                    string rightCode = DBConvert.GetString(dr, "RightCode");
                    if (!dic.ContainsKey(rightCode))
                    {
                        int rightID = DBConvert.GetInt32(dr, "RightID");
                        dic.Add(rightCode, rightID);
                    }
                }
            }
            return dic;
        }

     
        /// <summary>
        /// 添加新的pmusers
        /// </summary>
        /// <param name="pmusers">pmusers实体</param>
        /// <returns></returns>
        public static int Add(UsersModel pmusers)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(PM_USERS_INSERT);

            db.AddOutParameter(dbCommand, "@Id", DbType.Int32, 4);
            db.AddInParameter(dbCommand, "@UserName", DbType.String, DBConvert.ToDBValue(pmusers.UserName));
            db.AddInParameter(dbCommand, "@TrueName", DbType.String, DBConvert.ToDBValue(pmusers.TrueName));
            db.AddInParameter(dbCommand, "@UserIntegral", DbType.Decimal, DBConvert.ToDBValue(pmusers.UserIntegral));
            db.AddInParameter(dbCommand, "@UserPassword", DbType.String, DBConvert.ToDBValue(pmusers.UserPassword));
            db.AddInParameter(dbCommand, "@UserEmail", DbType.String, DBConvert.ToDBValue(pmusers.UserEmail));
            db.AddInParameter(dbCommand, "@UserEmailState", DbType.Boolean, pmusers.UserEmailState);
            db.AddInParameter(dbCommand, "@UserMobile", DbType.String, DBConvert.ToDBValue(pmusers.UserMobile));
            db.AddInParameter(dbCommand, "@UserMobileState", DbType.Boolean, pmusers.UserMobileState);
            db.AddInParameter(dbCommand, "@UserQQ", DbType.String, DBConvert.ToDBValue(pmusers.UserQQ));
            db.AddInParameter(dbCommand, "@UserMSN", DbType.String, DBConvert.ToDBValue(pmusers.UserMSN));
            db.AddInParameter(dbCommand, "@UserColourMoney", DbType.Decimal, pmusers.UserColourMoney);
            db.AddInParameter(dbCommand, "@UserMoney", DbType.Decimal, pmusers.UserMoney);
            db.AddInParameter(dbCommand, "@UserLoginTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.UserLoginTime));
            db.AddInParameter(dbCommand, "@UserRegisterTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.UserRegisterTime));
            db.AddInParameter(dbCommand, "@UserUpdateTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.UserUpdateTime));
            db.AddInParameter(dbCommand, "@UserImgUrl", DbType.String, DBConvert.ToDBValue(pmusers.UserImgUrl));
            db.AddInParameter(dbCommand, "@UserState", DbType.Int16, pmusers.UserState);
            db.AddInParameter(dbCommand, "@UserLoginState", DbType.Boolean, pmusers.UserLoginState);
            db.AddInParameter(dbCommand, "@UserRemark", DbType.String, DBConvert.ToDBValue(pmusers.UserRemark));
            db.AddInParameter(dbCommand, "@CreateTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.CreateTime));
            db.AddInParameter(dbCommand, "@CreateUser", DbType.Int32, DBConvert.ToDBValue(pmusers.CreateUser));
            db.AddInParameter(dbCommand, "@LastModifyTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.LastModifyTime));
            db.AddInParameter(dbCommand, "@LastModifyUser", DbType.Int32, DBConvert.ToDBValue(pmusers.LastModifyUser));


            int count = db.ExecuteNonQuery(dbCommand);

            if (count > 0)
                return (int)dbCommand.Parameters["@Id"].Value;
            return -1;
        }


        /// <summary>
        /// 根据Id获取指定的pmusers
        /// </summary>
        /// <param name="Id">pm_users主键</param>
        /// <returns></returns>
        public static UsersModel GetInfo(int id)
        {
            using (IDataReader dr = DBHelper.ExecuteReader(PM_USERS_SELECT_BY_ID,
                new SqlParameter() { ParameterName = "@Id", SqlDbType = SqlDbType.Int, Value = id }
                ))
            {
                return Emit.ConvertTo<UsersModel>(dr);
            }
        }
        /// <summary>
        /// 修改个人资料
        /// </summary>
        /// <param name="pmusers"></param>
        /// <returns></returns>
        public static bool UpdateUserMsg(UsersModel pmusers)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_USERS_updateMsg);
            db.AddInParameter(dbCommand, "@Id", DbType.Int32, DBConvert.ToDBValue(pmusers.Id));
            db.AddInParameter(dbCommand, "@TrueName", DbType.String, DBConvert.ToDBValue(pmusers.TrueName));
            db.AddInParameter(dbCommand, "@UserEmail", DbType.String, DBConvert.ToDBValue(pmusers.UserEmail));
            db.AddInParameter(dbCommand, "@UserMobile", DbType.String, DBConvert.ToDBValue(pmusers.UserMobile));
            db.AddInParameter(dbCommand, "@UserQQ", DbType.String, DBConvert.ToDBValue(pmusers.UserQQ));
            db.AddInParameter(dbCommand, "@UserImgUrl", DbType.String, DBConvert.ToDBValue(pmusers.UserImgUrl));
            return db.ExecuteNonQuery(dbCommand) > 0;
        }
        /// <summary>
        /// 修改WKF_WorkItemOfKit
        /// </summary>
        /// <param name="pmusers">pmusers实体</param>
        /// <returns></returns>
        public static bool Update(UsersModel pmusers)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(PM_USERS_UPDATE);

            db.AddInParameter(dbCommand, "@Id", DbType.Int32, DBConvert.ToDBValue(pmusers.Id));
            db.AddInParameter(dbCommand, "@UserName", DbType.String, DBConvert.ToDBValue(pmusers.UserName));
            db.AddInParameter(dbCommand, "@TrueName", DbType.String, DBConvert.ToDBValue(pmusers.TrueName));
            db.AddInParameter(dbCommand, "@UserIntegral", DbType.Decimal, DBConvert.ToDBValue(pmusers.UserIntegral));
            db.AddInParameter(dbCommand, "@UserPassword", DbType.String, DBConvert.ToDBValue(pmusers.UserPassword));
            db.AddInParameter(dbCommand, "@UserEmail", DbType.String, DBConvert.ToDBValue(pmusers.UserEmail));
            db.AddInParameter(dbCommand, "@UserEmailState", DbType.Boolean, (pmusers.UserEmailState));
            db.AddInParameter(dbCommand, "@UserMobile", DbType.String, DBConvert.ToDBValue(pmusers.UserMobile));
            db.AddInParameter(dbCommand, "@UserMobileState", DbType.Boolean, (pmusers.UserMobileState));
            db.AddInParameter(dbCommand, "@UserQQ", DbType.String, DBConvert.ToDBValue(pmusers.UserQQ));
            db.AddInParameter(dbCommand, "@UserMSN", DbType.String, DBConvert.ToDBValue(pmusers.UserMSN));
            db.AddInParameter(dbCommand, "@UserColourMoney", DbType.Decimal, (pmusers.UserColourMoney));
            db.AddInParameter(dbCommand, "@UserMoney", DbType.Decimal, (pmusers.UserMoney));
            db.AddInParameter(dbCommand, "@UserLoginTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.UserLoginTime));
            db.AddInParameter(dbCommand, "@UserRegisterTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.UserRegisterTime));
            db.AddInParameter(dbCommand, "@UserUpdateTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.UserUpdateTime));
            db.AddInParameter(dbCommand, "@UserImgUrl", DbType.String, DBConvert.ToDBValue(pmusers.UserImgUrl));
            db.AddInParameter(dbCommand, "@UserState", DbType.Int16, (pmusers.UserState));
            db.AddInParameter(dbCommand, "@UserLoginState", DbType.Boolean, (pmusers.UserLoginState));
            db.AddInParameter(dbCommand, "@UserRemark", DbType.String, DBConvert.ToDBValue(pmusers.UserRemark));
            db.AddInParameter(dbCommand, "@CreateTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.CreateTime));
            db.AddInParameter(dbCommand, "@CreateUser", DbType.Int32, DBConvert.ToDBValue(pmusers.CreateUser));
            db.AddInParameter(dbCommand, "@LastModifyTime", DbType.DateTime, DBConvert.ToDBValue(pmusers.LastModifyTime));
            db.AddInParameter(dbCommand, "@LastModifyUser", DbType.Int32, DBConvert.ToDBValue(pmusers.LastModifyUser));


            return db.ExecuteNonQuery(dbCommand) > 0;
        }
    }
}
